![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Packages, Procedures, and FunctionsAnother way to improve performance of your SQL statements is by using packages, procedures, and functions. Packages can help improve performance by storing together procedures and functions that are often used together. By storing these elements together, you can reduce the I/O required to bring them into memory from disk. Because these elements are often used together, they can also be loaded from disk together. By using stored procedures, you benefit in several ways. Stored procedures allow you to reduce the amount of data sent across the network. The stored procedure requires fewer instructions to be sent to the server; in many cases, less data must be sent back to the client from the server. A second benefit of a stored procedure is the increased chance that the SQL statement can be used by other processes. Because the SQL statement is defined and used by many processes, chances are good that the SQL statement will already be parsed in the shared SQL area and available to other users. Chapter 28, Using Procedures, Functions, and Packages, covers how to use these elements to improve the performance of SQL statements. Optimization ApproachesOracle offers several options for optimization techniques. Among these are a cost-based approach and a rule-based approach. The approach you take depends both on your application and your data. In most cases, the cost-based approach is recommended because it determines an execution plan that is as good or better than the rule-based approach. The following sections look at the optimization approaches available from Oracle and when each approach is appropriate. Remember that you can use hints to specifically tell Oracle how you want the SQL statement to be executed. There are several ways you can indicate your preference, as described later in this chapter. The discussion here is limited to an overview because the Oracle optimizer is detailed in Chapter 27, Using the Oracle Optimizer. Rule-Based Approach The rule-based approach to Oracle optimization is straightforward and consistent. In the rule-based approach, the execution plan is derived by examining the available paths and ranking them against a list of predetermined values for these paths (see Figure 26.2).
With the rule-based optimization approach, the optimizer determines the ways to execute the SQL statement. If there is more than one way to execute the SQL statement, the table in Figure 26.2 is used to choose the approach with the lowest ranking. Cost-Based Approach The cost-based approach to optimization uses existing knowledge of the database to choose the most efficient execution plan. During the normal operation of the RDBMS, statistics are gathered on the data distribution and storage characteristics. The optimizer uses this information to determine the most optimal execution plan. This optimization approach takes three steps:
The cost-based approach is usually preferred. In some cases, the rule-based approach may be more appropriate, as discussed in Chapter 27, Using the Oracle Optimizer. Hints You can use hints to inform the optimizer of some special facts you know about the data or the SQL statement that may affect the execution plan. By using hints, you indicate that the SQL statement may be more efficient by using a certain execution plan (for example, a full-table scan or increased parallelism). Hints are detailed in Chapter 30, Using Hints.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |